#!/bin/bash
source /etc/profile

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
select SUBSTR(transaction_date,1,16),station_id,count(DISTINCT transaction_id),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value
from ( SELECT transaction_date,transaction_id,station_id from transactions where substr(transaction_date,1,10)='$dt' ) as Z 
join transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id)AS X
group by SUBSTR(transaction_date,1,16),station_id;
EOF
